﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;

namespace ProgNetComponents.Data
{
    public abstract class SchemaChecker
    {
        private static Dictionary<string, SchemaKeeper> _AllSchemas = new Dictionary<string, SchemaKeeper>();
        public static bool TableExists(string connectionString, string table)
        {
            bool res = false;
            if (_AllSchemas.ContainsKey(connectionString.AsString().ToUpper()))
            {
                SchemaKeeper k = _AllSchemas[connectionString.AsString().ToUpper()];
                res = k.Table.RowExists(k.TableNameColumn + " = " + table.EscapeSql());
            }
            return res;
        }

        public static bool ColumnExists(string connectionString, string table, string column)
        {
            bool res = false;
            if (_AllSchemas.ContainsKey(connectionString.AsString().ToUpper()))
            {
                SchemaKeeper k = _AllSchemas[connectionString.AsString().ToUpper()];
                res = k.Table.RowExists(k.TableNameColumn + " = " + table.EscapeSql() + " AND " + k.ColumnNameColumn + " = " + column.EscapeSql());
            }
            return res;
        }

        public static bool ColumnExists(string connectionString, DataTable table, DataColumn column)
        {
            return ColumnExists(connectionString, table.TableName, column.ColumnName);
        }
        
        private DataTable _ColumnsTable;
        private string _ConnectionString;
        private DbDataAdapter _Adapter;

        public string ConnectionString
        {
            get { return _ConnectionString; }
        }
        
        protected SchemaChecker(string ConnectionString)
        {
            _ConnectionString = ConnectionString;
            GetSchema();
        }

        protected DataTable ColumnsTable
        {
            get
            {
                if (_ColumnsTable == null)
                {
                    _ColumnsTable = new DataTable(TableName);
                    _ColumnsTable.Columns.Add(TableNameColumn);
                    _ColumnsTable.Columns.Add(ColumnNameColumn);
                }
                return _ColumnsTable;
            }

        }
        protected DbDataAdapter Adapter
        {
            get
            {
                if (_Adapter == null)
                    _Adapter = CreateAdapter(ConnectionString, GetSelectString());
                return _Adapter;
            }
        }

        protected abstract string TableNameColumn { get; }
        protected abstract string ColumnNameColumn { get; }
        protected abstract string TableName { get; }
        protected abstract DbDataAdapter CreateAdapter(string connectionString, string selectString);

        protected virtual string GetSelectString()
        {
            return "SELECT " + TableNameColumn + "," + ColumnNameColumn + " FROM " + TableName;
        }
        public virtual void GetSchema()
        {
            if (!_AllSchemas.ContainsKey(ConnectionString.AsString().ToUpper()))
                RefreshSchema();
            else
                this._ColumnsTable = _AllSchemas[ConnectionString.AsString().ToUpper()].Table;
        }
        public virtual void RefreshSchema()
        {
            Adapter.SelectCommand.CommandText = GetSelectString();
            Adapter.SelectCommand.Connection.ConnectionString = ConnectionString;
            ColumnsTable.Clear();
            Adapter.Fill(ColumnsTable);
            if (!_AllSchemas.ContainsKey(ConnectionString.AsString().ToUpper()))
                _AllSchemas.Add(ConnectionString.AsString().ToUpper(), new SchemaKeeper(TableNameColumn, ColumnNameColumn, ColumnsTable));
        }

        public virtual bool TableExists(string table)
        {
            return TableExists(ConnectionString, table);
        }
        public virtual bool ColumnExists(string table, string column)
        {
            return ColumnExists(ConnectionString, table, column);
        }
        public virtual bool ColumnExists(DataTable table, DataColumn column)
        {
            return ColumnExists(ConnectionString, table, column);
        }
        public virtual List<string> GetTableColumns(string table)
        {
            GetSchema();
            List<string> l = new List<string>();
            DataRow[] rows = ColumnsTable.Select(TableNameColumn + " = " + table.EscapeSql());
            foreach (DataRow r in rows)
            {
                l.Add(r[ColumnNameColumn].AsString());
            }
            return l;
        }
        private class SchemaKeeper
        {
            public SchemaKeeper(string tableColName, string columnColName, DataTable dt)
            {
                this.ColumnNameColumn = columnColName;
                this.TableNameColumn = tableColName;
                this.Table = dt;
            }

            public string TableNameColumn
            {
                get;
                set;
            }
            public string ColumnNameColumn
            {
                get;
                set;
            }
            public DataTable Table
            {
                get;
                set;
            }

        }
    }
    
}
